﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class WelfareDataBLL


    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);




        public void insert(string wef_name, string remarks, int ext_seq, int created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@wef_name", wef_name);
                dbManager.AddParameters(1, "@ext_seq", ext_seq);
                dbManager.AddParameters(2, "@remarks", remarks);
                dbManager.AddParameters(3, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@created_by", created_by);
                dbManager.AddParameters(5, "@created_when", DateTime.Today);
            
           
  
                string sql;



                sql = " insert into hrm_wf_welfare_types(wef_name,ext_seq,remarks,record_status,created_by,created_when) "
                + " values(@wef_name,@ext_seq,@remarks,@record_status,@created_by,@created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);    

            }

            catch(Exception ex)
             {

                 throw new Exception(ex.Message);  
            }

          finally 
                 {
                     dbManager.Dispose(); 

            }

        }



        public DataTable Select()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "SELECT "
+ " wf.wef_name,wf.wef_seq,ext.ext_name,iif(ext.ext_flag='I','เงินเพิ่ม','เงินลด')as ext_flag_name,ext_flag,wf.remarks,wf.ext_seq"
+ " FROM"
+ " hrm_wf_welfare_types AS wf"
+ " Inner Join hrm_pr_extra_types AS ext ON wf.ext_seq = ext.ext_seq"
+ " and wf.record_status = '" + SystemInfo._ActiveRecordStatus + "'";

                    

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }





        public DataTable SelectExtByID(int ext_seq)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT ext_seq,ext_name"
+ " from hrm_pr_extra_types"
+ " where ext_seq='" + ext_seq + "'"
+ " and record_status = '" + SystemInfo._ActiveRecordStatus + "'";


                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }




        public DataTable SelectExt(string ext_flag)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT ext_seq,ext_name,ext_flag"
+ " from hrm_pr_extra_types"
+ " where ext_flag='" + ext_flag + "'"
+ " and record_status = '" + SystemInfo._ActiveRecordStatus + "'";


                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }






        public void Update(int wef_seq, string wef_name, string remarks,int ext_seq, int updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@wef_seq", wef_seq);
                dbManager.AddParameters(1, "@wef_name", wef_name);
                dbManager.AddParameters(2, "@remarks", remarks);
                dbManager.AddParameters(3, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@updated_by", updated_by);
                dbManager.AddParameters(5, "@updated_when", DateTime.Today);
                dbManager.AddParameters(6, "@ext_seq", ext_seq); 

                string sql;

                sql = " update hrm_wf_welfare_types "
         + " set ext_seq=@ext_seq,wef_name=@wef_name,remarks=@remarks,record_status=@record_status,updated_by=@updated_by,"
         + " updated_when=@updated_when"
         + " where wef_seq=@wef_seq";


   

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {
            }

            finally
            {
                dbManager.Dispose();

            }

        }



        public void Delete(int wef_seq, int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@wef_seq", wef_seq);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string sql;
             
                sql = " update hrm_wf_welfare_types "
              + " set record_status=@record_status"
              + ", updated_by = @updated_by"
              + ", updated_when = @updated_when"
              + " where wef_seq = @wef_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
      
            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public Boolean chkDupWelfare( string wef_name)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "select * from hrm_wf_welfare_types"
                    + " where wef_name = '" + wef_name + "'"
                    + " and record_status='" + SystemInfo._ActiveRecordStatus + "'";
                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }



            }

            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }

            finally
            {
                dbManager.Dispose();
            }


        }

        public Boolean chkDupWelfareEdit( string wef_name, int wef_seq)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "select * from hrm_wf_welfare_types"
                    + " where wef_name = '" + wef_name + "'"
                    + " and wef_seq not like " + wef_seq
                    + " and record_status='" + SystemInfo._ActiveRecordStatus + "'";
                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }



            }

            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }

            finally
            {
                dbManager.Dispose();
            }


        }




     

   
    }
}
